﻿using System;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Collections.Generic;
using Shuba.Model;
using System.Diagnostics;

namespace Shuba.Data
{
    public class BulkUp
    {
        public BulkUp()
        {
        }

        public BulkUpInfo Get(string BATCH_ID)
        {
            Database db = DBConnection.CreateDatabase();
            BulkUpInfo _list = new BulkUpInfo();

            DbCommand mycomm = db.GetSqlStringCommand(@"SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE]
                              ,[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE]
                        FROM [TBL_BULK_UPLOAD]
                         WHERE BATCH_ID = @BATCH_ID");
            db.AddInParameter(mycomm, "@BATCH_ID", DbType.String, BATCH_ID);

            using (IDataReader mydr = db.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list = new BulkUpInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetInt32(5), mydr.GetInt32(6), mydr.GetInt32(7), mydr.GetString(8));
                }
                mydr.Close();
            }
            return _list;
        }

        public List<BulkUpInfo> GetList(string BATCH_ID)
        {
            Database mydb = DBConnection.CreateDatabase();
            List<BulkUpInfo> _list = new List<BulkUpInfo>();

            string mysql = String.Format(@"SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE]
                                                  ,[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE]
                                            FROM [TBL_BULK_UPLOAD]
                                             WHERE UPLOAD_TYPE = 'PIPELINE' AND BATCH_ID LIKE '%{0}%' ORDER BY UPLOAD_DATE", BATCH_ID);
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list.Add(new BulkUpInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetInt32(5), mydr.GetInt32(6), mydr.GetInt32(7), mydr.GetString(8)));
                }
                mydr.Close();
            }
            return _list;
        }

        public List<BulkUpInfo> GetListTopupMonitor(string BATCH_ID)
        {
            Database mydb = DBConnection.CreateDatabase();
            List<BulkUpInfo> _list = new List<BulkUpInfo>();

            string mysql = String.Format(@"SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE]
                                                  ,[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE]
                                            FROM [TBL_BULK_UPLOAD]
                                             WHERE UPLOAD_TYPE = 'TOPUP' AND IS_DELETED = '0' AND BATCH_ID LIKE '%{0}%' ORDER BY UPLOAD_DATE", BATCH_ID);
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list.Add(new BulkUpInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetInt32(5), mydr.GetInt32(6), mydr.GetInt32(7), mydr.GetString(8)));
                }
                mydr.Close();
            }
            return _list;
        }

        public List<TopUpBulkInfo> GetListTopupFailed(string value, string batch_id)
        {
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT TMP.[BATCH_ID],TMP.[CIF_NUMBER],TMP.[LOAN_NUMBER]
                          ,TMP.[UNIT_CODE],ISNULL(TBL_MS_BRANCH.BRANCH_NAME,'') BRANCH_NAME, ISNULL(TBL_MS_CLUSTER.CLUSTER_NAME,'') CLUSTER_NAME
	                      ,ISNULL(TBL_MS_REGIONAL.REGIONAL_NAME,'') REGIONAL_NAME,TMP.[DEBITOR_NAME],TMP.[PRODUCT_CODE]
                          ,TMP.[CURRENT_OS],TMP.[CURRENT_INSTALLMENT],TMP.[OFFER_PLAFON]
                          ,TMP.[OFFER_INSTALLMENT],TMP.[OFFER_INTEREST],TMP.[OFFER_TENOR]
                          ,TMP.[OFFER_FRESH_MONEY],TMP.[PRIORITY],TMP.[JENIS_USAHA]
                          ,TMP.[KODYA_KAB_BUSINESS],TMP.[KECAMATAN_BUSINESS],TMP.[KELURAHAN_BUSINESS]
                          ,TMP.[ZIP_CODE_BUSINESS],TMP.[ADDRESS_BUSINESS],TMP.[MOBILE_PHONE]
                          ,TMP.[OFFICE_PHONE],TMP.[RESIDENTIAL_PHONE],TMP.[DATE_VISIT_PLAN]
                          ,TMP.[ENTRY_BY],TMP.[ENTRY_DATE],TMP.[UPDATE_BY]
                          ,TMP.[UPDATE_DATE],TMP.[PROSPECT_TYPE],TMP.[DEBTOR_TYPE]
                          ,TMP.[CHANNELING],TMP.[EKSTERNAL],TMP.[REFERENSI]
                          ,TMP.[ERR_LINE],TMP.[ERR_DESC],TMP.[ACT_TYPE]
                    FROM [TBL_TOPUP_TMP] TMP LEFT JOIN 
	                    TBL_MS_BRANCH ON TMP.UNIT_CODE = TBL_MS_BRANCH.BRANCH_ID LEFT JOIN
                        TBL_MS_CLUSTER_BRANCH ON TBL_MS_BRANCH.BRANCH_ID = TBL_MS_CLUSTER_BRANCH.BRANCH_ID LEFT JOIN
                        TBL_MS_CLUSTER ON TBL_MS_CLUSTER_BRANCH.CLUSTER_ID = TBL_MS_CLUSTER.CLUSTER_ID LEFT JOIN
                        TBL_MS_REGION_CLUSTER ON TBL_MS_CLUSTER.CLUSTER_ID = TBL_MS_REGION_CLUSTER.CLUSTER_ID LEFT JOIN
                        TBL_MS_REGIONAL ON TBL_MS_REGION_CLUSTER.REGIONAL_ID = TBL_MS_REGIONAL.REGIONAL_ID	
                    WHERE [BATCH_ID] = @BATCH_ID AND ERR_LINE > 0 AND ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%')) ORDER BY UPDATE_DATE DESC";

            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetDouble(11), dr.GetDouble(12), dr.GetString(13), dr.GetInt16(14), dr.GetDouble(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetString(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetDateTime(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetInt32(36), dr.GetString(37), dr.GetString(38)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetListTopupSuccess(string value, string batch_id)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP WHERE BATCH_ID = @BATCH_ID AND (CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpBulkInfo> GetListTopupApproval(string value, string batch_id)
        {
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [BATCH_ID],[CIF_NUMBER],[LOAN_NUMBER]
                      ,[UNIT_CODE],[DEBITOR_NAME],[PRODUCT_CODE]
                      ,[CURRENT_OS],[CURRENT_INSTALLMENT],[OFFER_PLAFON]
                      ,[OFFER_INSTALLMENT],[OFFER_INTEREST],[OFFER_TENOR]
                      ,[OFFER_FRESH_MONEY],[PRIORITY],[JENIS_USAHA]
                      ,[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS],[KELURAHAN_BUSINESS]
                      ,[ZIP_CODE_BUSINESS],[ADDRESS_BUSINESS],[MOBILE_PHONE]
                      ,[OFFICE_PHONE],[RESIDENTIAL_PHONE],[DATE_VISIT_PLAN]
                      ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY]
                      ,[UPDATE_DATE],[PROSPECT_TYPE],[DEBTOR_TYPE]
                      ,[CHANNELING],[EKSTERNAL],[REFERENSI]
                      ,[ERR_LINE],[ERR_DESC],[ACT_TYPE]  
                FROM [TBL_TOPUP_TMP] WHERE [BATCH_ID] = @BATCH_ID AND STATUS = 'P' AND ERR_LINE = 0 AND (CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetInt32(33), dr.GetString(34), dr.GetString(35)));
                }
                dr.Close();
            }
            return list;
        }


        public List<PipelineBulkInfo> GetListPipelineSuccess(string value, string batch_id)
        {
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE WHERE [BATCH_ID] = @BATCH_ID AND (UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetDateTime(41), dr.GetString(42)));
                }
                dr.Close();
            }
            return list;

        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public int ExecuteCmdShell(string xp_cmdshellPath, string fileUploadPath, string uploadType, string UploadMaker)
        {
            int status = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();
                xp_cmdshellPath = RebuiltPath(xp_cmdshellPath);
                DbCommand mycomm = mydb.GetSqlStringCommand(@"exec xp_cmdshell '" + xp_cmdshellPath + " \"" + fileUploadPath + "|" + uploadType + "|" + UploadMaker + "\"'");

                status = mydb.ExecuteNonQuery(mycomm);
            }
            catch (Exception ex) { }
            return status;
        }

        public string ExecuteCommand(string xp_cmdshellPath, string fileUploadPath, string uploadType, string UploadMaker)
        {
            string ret = "";
            try
            {
                string command = String.Format("{0}|{1}|{2}", fileUploadPath, uploadType, UploadMaker);
                ProcessStartInfo ProcessInfo = new ProcessStartInfo(xp_cmdshellPath, String.Format("\"{0}\"", command));
                ProcessInfo.CreateNoWindow = true;
                ProcessInfo.UseShellExecute = false;
                Process Process = Process.Start(ProcessInfo);
            }
            catch (Exception ex)
            {
                ret = ex.ToString();
            }
            return ret;
        }


        private string RebuiltPath(string origPath)
        {
            string hasil = "";
            string[] split = origPath.Split('\\');

            foreach (string item in split)
            {
                if (item.Contains(" "))
                {
                    string tambahpetik = "";
                    string[] temp = item.Split(' ');
                    for (int i = 0; i < temp.Length; i++)
                    {
                        if (i == 0) tambahpetik += "\"" + temp[0] + " ";
                        else if (i == (temp.Length - 1)) tambahpetik += temp[temp.Length - 1] + "\"";
                        else if (temp[i] == "") tambahpetik += " ";
                        else tambahpetik += temp[i] + " ";
                    }
                    hasil += tambahpetik + "\\";
                }
                else
                {
                    hasil += item + "\\";
                }
            }
            hasil = hasil.Substring(0, hasil.Length - 1);
            return hasil;
        }

        public int GetRowNumberTopUpMonitor(string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED]
                    FROM [TBL_BULK_UPLOAD] WHERE UPLOAD_TYPE = 'TOPUP' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_DELETED = '0') AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }
        public int GetRowNumberMaintenanceVisitMonitor(string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED]
                    FROM [TBL_BULK_UPLOAD] WHERE UPLOAD_TYPE = 'MAINTENANCE_VISIT' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_DELETED = '0') AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<BulkUpInfo> GetListTopUpMonitoring(string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<BulkUpInfo> list = new List<BulkUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM ( 
                        SELECT ROW_NUMBER() OVER (ORDER BY UPLOAD_DATE DESC) AS Row, 
                        [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE] 
                   FROM [TBL_BULK_UPLOAD] WHERE UPLOAD_TYPE = 'TOPUP' AND BATCH_ID LIKE @BATCH_ID AND IS_DELETED = '0' 
                   ) AS LogWithRowNumbers 
                   WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, "%" + batch_id + "%");

            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9)));
                }
                dr.Close();
            }
            return list;
        }
        public List<BulkUpInfo> GetListMaintenanceVisitMonitoring(string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<BulkUpInfo> list = new List<BulkUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM ( 
                        SELECT ROW_NUMBER() OVER (ORDER BY UPLOAD_DATE DESC) AS Row, 
                        [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE] 
                   FROM [TBL_BULK_UPLOAD] WHERE UPLOAD_TYPE = 'MAINTENANCE_VISIT' AND BATCH_ID LIKE @BATCH_ID AND IS_DELETED = '0' 
                   ) AS LogWithRowNumbers 
                   WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, "%" + batch_id + "%");

            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowNumberApprovalMaintenanceVisitMonitor(string update_by, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED]
                    FROM [TBL_BULK_UPLOAD] WHERE UPDATE_BY <> '' + @UPDATE_BY + '' AND UPLOAD_TYPE = 'MAINTENANCE_VISIT' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_PENDING_APPROVE = 'P') AS DATA";
            //UPLOAD_TYPE = 'TOPUP' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_PENDING_APPROVE = '0'
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }
        public int GetRowNumberaApprovalTopUpMonitor(string update_by, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED]
                    FROM [TBL_BULK_UPLOAD] WHERE UPDATE_BY <> '' + @UPDATE_BY + '' AND UPLOAD_TYPE = 'TOPUP' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_PENDING_APPROVE = 'P') AS DATA";
            //UPLOAD_TYPE = 'TOPUP' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_PENDING_APPROVE = '0'
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<BulkUpInfo> GetListApprovalTopUpMonitoring(string update_by, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<BulkUpInfo> list = new List<BulkUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row," +
                  "[BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE],[IS_DELETED] " +
                  "FROM [TBL_BULK_UPLOAD] WHERE UPDATE_BY <> '' + @UPDATE_BY + '' AND UPLOAD_TYPE = 'TOPUP' AND IS_PENDING_APPROVE = 'P' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' " +
                  ") AS LogWithRowNumbers " +
                  "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9), dr.GetString(10)));
                }
                dr.Close();
            }
            return list;
        }
        public List<BulkUpInfo> GetListApprovalMaintenanceVisitMonitoring(string update_by, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<BulkUpInfo> list = new List<BulkUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT * FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row," +
                  "[BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE],[IS_DELETED] " +
                  "FROM [TBL_BULK_UPLOAD] WHERE UPDATE_BY <> '' + @UPDATE_BY + '' AND UPLOAD_TYPE = 'MAINTENANCE_VISIT' AND IS_PENDING_APPROVE = 'P' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' " +
                  ") AS LogWithRowNumbers " +
                  "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9), dr.GetString(10)));
                }
                dr.Close();
            }
            return list;
        }

        public void delete(string batch_id, string upload_type)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_BULK_UPLOAD SET IS_DELETED = '1', IS_PENDING_APPROVE = 'P' WHERE BATCH_ID = @BATCH_ID AND UPLOAD_TYPE = @UPLOAD_TYPE");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@UPLOAD_TYPE", DbType.String, upload_type);
            db.ExecuteNonQuery(cmd);
        }

        public int approve(string batch_id)
        {
            Int32 value;
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_BULK_UPLOAD SET IS_PENDING_APPROVE = 'A' WHERE BATCH_ID = @BATCH_ID");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            value = db.ExecuteNonQuery(cmd);
            return value;
        }

        public int reject(string batch_id, string is_delete)
        {
            Int32 value;
            Database db = DBConnection.CreateDatabase();
            string sql = @"IF(@IS_DELETED = '1')
                           BEGIN
                            UPDATE TBL_BULK_UPLOAD SET IS_DELETED = '0',IS_PENDING_APPROVE = '' WHERE BATCH_ID = @BATCH_ID
                           END
                           ELSE 
                           UPDATE TBL_BULK_UPLOAD SET IS_PENDING_APPROVE = 'A' WHERE BATCH_ID = @BATCH_ID";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@IS_DELETED", DbType.String, is_delete);
            value = db.ExecuteNonQuery(cmd);
            return value;
        }

        public int ApproveMaintenanceVisitMonitoring(string batch_id, string status)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("MAINTENANCE_VISIT_MONITORING_APPROVAL");

            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@IS_DELETED", DbType.String, status);

            return db.ExecuteNonQuery(cmd);
        }

        public int ApproveTopupMonitoring(string batch_id, string status)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("TOPUP_MONITORING_APPROVAL");

            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@IS_DELETED", DbType.String, status);

            return db.ExecuteNonQuery(cmd);
        }

        public int getNumRecordperBatchid(string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT * FROM TBL_TOPUP_TMP WHERE BATCH_ID = @BATCH_ID) AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public BulkUpInfo GetApprovalByID(string batch_id)
        {
            BulkUpInfo ci = new BulkUpInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_BULK_UPLOAD WHERE BATCH_ID = @BATCH_ID AND IS_PENDING_APPROVE = '0'");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ci;
        }

        public int GetRowNumberPipelineMonitor(string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED]
                    FROM [TBL_BULK_UPLOAD] WHERE UPLOAD_TYPE = 'PIPELINE' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_DELETED = '0') AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<BulkUpInfo> GetListpipelineMonitoring(string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<BulkUpInfo> list = new List<BulkUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM ( 
                  SELECT ROW_NUMBER() OVER (ORDER BY UPLOAD_DATE DESC) AS Row,
                    [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE],[IS_DELETED] 
                  FROM [TBL_BULK_UPLOAD] WHERE UPLOAD_TYPE = 'PIPELINE' AND BATCH_ID LIKE @BATCH_ID AND IS_DELETED = '0'
                  ) AS LogWithRowNumbers 
                  WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, "%" + batch_id + "%");
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9), dr.GetString(10)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowNumberApprovalPipelineMonitor(string update_by, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT [BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED]
                    FROM [TBL_BULK_UPLOAD] WHERE UPDATE_BY <> '' + @UPDATE_BY + '' AND UPLOAD_TYPE = 'PIPELINE' AND BATCH_ID LIKE '%' + @BATCH_ID + '%' AND IS_PENDING_APPROVE = 'P') AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<BulkUpInfo> GetListApprovalpipelineMonitoring(string update_by, string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<BulkUpInfo> list = new List<BulkUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY UPLOAD_DATE DESC) AS Row," +
                  "[BATCH_ID],[UPLOAD_TYPE],[UPLOAD_BY],[UPLOAD_DATE],[STATUS],[NUM_OF_RECORD],[RECORD_SUCCESS],[RECORD_FAILED],[IS_PENDING_APPROVE],[IS_DELETED] " +
                  "FROM [TBL_BULK_UPLOAD] WHERE UPDATE_BY <> '' + @UPDATE_BY + '' AND UPLOAD_TYPE = 'PIPELINE' AND BATCH_ID LIKE @BATCH_ID AND IS_PENDING_APPROVE = 'P'" +
                  ") AS LogWithRowNumbers " +
                  "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, "%" + batch_id + "%");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BulkUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(7), dr.GetInt32(8), dr.GetString(9), dr.GetString(10)));
                }
                dr.Close();
            }
            return list;
        }

        //
        public int ApprovePipelineMonitoring(string batch_id, string is_delete)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("[PIPELINE_MONITORING_APPROVAL]");

            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@IS_DELETED", DbType.String, is_delete);

            return db.ExecuteNonQuery(cmd);
        }

        public int GetRowNumberPipelinebyBatchID(string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT * FROM TBL_PIPELINE_TMP WHERE STATUS ='P' AND BATCH_ID = '' +@BATCH_ID+'') AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberTopupbyBatchID(string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT * FROM TBL_TOPUP_TMP WHERE STATUS ='P' AND BATCH_ID = '' +@BATCH_ID+'') AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }
    }
}
